Dynamic Partition Pruning

In data warehousing, datasets are typically organized in a Star Schema, consisting of a massive, partitioned Fact Table (e.g., sales partitioned by date) and multiple small Dimension Tables (e.g., stores or products).

Traditional database query engines apply filters to dimension tables, join the results with the fact table, and scan all partitioned directories in the fact table. This is extremely slow. Dynamic Partition Pruning (DPP) resolves this by skipping unnecessary partition files at runtime.

Static vs. Dynamic Partition Pruning

• Static Partition Pruning: Occurs when you write a filter directly on the partition column: df.filter(col("date") == "2026-05-23") Spark looks at the path, identifies the directory /date=2026-05-23/, and scans only that folder. Extremely fast.

• Dynamic Partition Pruning (DPP): Occurs during a Join. If your query is: sales_df.join(date_dimension_df, "date").filter(col("state") == "California") date is a partition column in sales_df, but the filter state = 'California' is on the dimension table. At runtime, Spark first scans date_dimension_df to find dates in California, compiles a list of matching dates, and dynamically injects this list as a filter on sales_df, pruning unneeded fact partitions before scanning them!

How DPP Works (Under the Hood)

Step 1: Scan & Filter Dimension Table  [Result: Date list matches 'California' (e.g. 2026-05-23)]

                                     Injected at Runtime

Step 2: Prune Fact Table Partitions  Skip all dates EXCEPT 2026-05-23 on disk scan!

By avoiding reading billions of rows from non-matching partition files, DPP drastically cuts down disk I/O and network shuffles.

PySpark Code Example: star-schema DPP

DPP is enabled by default in Spark 3.0+. To trigger it, ensure:

1. Your large fact table is physically partitioned by a column (e.g. date).
2. Your small dimension table is joined on that partition key, and a filter is applied to a non-partitioned dimension column.

from pyspark.sql import SparkSession
from pyspark.sql.functions import col

# 1. Setup Spark enabling DPP (which is default true)
spark = SparkSession.builder \
    .appName("Dynamic Partition Pruning") \
    .config("spark.sql.optimizer.dynamicPartitionPruning.enabled", "true") \
    .master("local[*]") \
    .getOrCreate()

# 2. Large Fact Table: Sales partitioned by 'date'
sales_df = spark.range(1, 10000000) \
                .withColumn("date", col("id") % 10) \
                .withColumn("amount", col("id") * 1.5)

# Save as a partitioned table in the Catalog
sales_df.write \
        .format("parquet") \
        .mode("overwrite") \
        .partitionBy("date") \
        .saveAsTable("partitioned_sales")

# 3. Small Dimension Table: Dates
dates_data = [(i, f"Day_{i}", "Weekend" if i % 7 == 0 else "Weekday") for i in range(10)]
dates_df = spark.createDataFrame(dates_data, ["date", "day_name", "day_type"])
dates_df.createOrReplaceTempView("dates_dim")

# 4. Read partitioned table
fact_sales = spark.table("partitioned_sales")

# 5. Join applying a filter to the Dimension Table
# This triggers DPP, dynamically scanning only the partitions matching "Weekend"!
dpp_df = fact_sales.join(dates_df, "date") \
                   .filter(col("day_type") == "Weekend")

dpp_df.show()

# 6. Check the plan
# You will see 'DynamicPartitionPruning' in the scanned physical plan step!
dpp_df.explain()